package com.bluegalaxy.nhb.snservice.core.util;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.logging.log4j.util.PropertiesUtil;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;

/**
 * Created by XueQB on 2016/8/16.
 * 使用JXL处理Excel文件
 */
public class JXLExcelUtil {

    private static Log log = LogFactory.getLog(JXLExcelUtil.class);


    /**
     * 当前行处理
     *
     * @param cells 当前行
     * @return 处理后的结果
     */
    public static String[] readRow(Cell[] cells) {
        String[] row = new String[cells.length];
        String isEmpty = "";//判断当前行是否为空行
        for (int col = 0; col < cells.length; col++) {
            Cell cell = cells[col];
            if (cell == null) {
                row[col] = "";
            } else {
                if (cell.getType() == CellType.DATE) {
                    String value = formateData(cell);
                    row[col] = value == null ? "" : value.trim();
                } else {
                    String value = cell.getContents();
                    row[col] = value == null ? "" : value.trim();
                }
            }
            isEmpty += row[col];
        }
        return isEmpty.equals("") ? null : row;
    }

    /**
     * 解析Excel的Sheet页
     *
     * @param s 要解析的sheet页
     * @return Collection
     */

    public static Collection readsheet(Sheet s) {
        ArrayList rows = new ArrayList();
        for (int i = 0; i < s.getRows(); i++) {
            ArrayList cols = new ArrayList();
            for (int j = 0; j < s.getColumns(); j++) {
                Cell cell = s.getCell(j, i);
                if (cell != null) {
                    if (cell.getType() == CellType.DATE) {
                        // DateCell datecell = (DateCell) cell;
                        String value = formateData(cell);
                        if (value == null)
                            value = "";
                        cols.add(value);
                    } else {
                        String value = cell.getContents();
                        if (value == null)
                            value = "";
                        cols.add(value);
                    }
                } else {
                    cols.add("");
                }
            }
            rows.add(cols);
        }
        return rows;
    }

    /**
     * jxl 单元格 日期格式化处理
     * 默认处理格式：yyyy-MM-dd HH:mm:ss
     *
     * @param formatecell
     * @return String 格式化
     */
    public static String formateData(Cell formatecell) {
        try {
            java.util.Date mydate = null;
            DateCell datecll = (DateCell) formatecell;
            mydate = datecll.getDate();
            long time = (mydate.getTime() / 1000) - 60 * 60 * 8;
            mydate.setTime(time * 1000);
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return formatter.format(mydate);
        } catch (Exception e) {
            log.error("时间格式转换失败");
            e.printStackTrace();
            return null;
        }
    }
}
